package com.scalar.chiptrack.operations.process.cycleTime.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import org.apache.struts.util.LabelValueBean;

import com.scalar.ScalarException;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.operations.process.VendorLocation;
import com.scalar.chiptrack.operations.process.cycleTime.StageDetails;
import com.scalar.chiptrack.operations.process.cycleTime.forms.StageCycleTimeForm;
import com.scalar.chiptrack.utils.SqlQueryMappings;

/**
 * Created By Ganesh
 * Date  : Apr 3, 2006
 * Time  : 3:13:11 PM
 */
public class StageCycleTimeDAO
{

    public static ArrayList loadAssemblyVendorLocations( DBTransaction dbTransaction ) throws ScalarException
    {
       Connection        conn      = null;
       PreparedStatement pstmt     = null;
       ResultSet         rs        = null;
       ArrayList         locations = null;
       VendorLocation vendorLocation=null;

       try
       {
           if ( dbTransaction == null )
           {
               conn = DataSource.getConnection();
           }
           else
           {
               conn = dbTransaction.getConnection();
           }

           pstmt = conn.prepareStatement ( SqlQueryMappings.get("operations.process.vendorLocation.AssemblyLocations") );
           rs = pstmt.executeQuery();

           locations = new ArrayList();

           while ( rs.next() )
           {
               vendorLocation = new VendorLocation();
               vendorLocation.setLocationName( rs.getString("LOCATION_NAME") );
               vendorLocation.setLocationID( rs.getInt("LOCATION_ID") + "" );
               locations.add( vendorLocation );
           }
       }
       catch(SQLException sql)
       {
           sql.printStackTrace();
           throw new ScalarException(" Could not load Assembly venodrs in AssemblyProcessDAO", sql);
       }
       finally
       {
           if(rs != null)
           {
               try{ rs.close();}catch(Exception e){}
           }
           if(pstmt != null)
           {
               try{ pstmt.close(); }catch(Exception e){}
           }
           if(conn != null)
           {
               try{ conn.close(); }catch(Exception e){}
           }
       }

       return locations;
    }

    public static ArrayList loadAssemblyPartNumbers( DBTransaction dbTransaction, int locationId ) throws ScalarException
    {
        Connection conn = null;
	    PreparedStatement pstmt = null;
	    ResultSet rs = null;
        ArrayList partNumbersList = new ArrayList();

	    try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement( SqlQueryMappings.get( "operations.process.vendorPart.AssemblyPartNumbers" ) );
            pstmt.setInt( 1, locationId );
            rs = pstmt.executeQuery();

            while ( rs.next() )
            {
                partNumbersList.add( new LabelValueBean( rs.getString("PART_NUMBER"), rs.getString("PROCESSSTEP_ID") ) );
            }
        }
        catch(SQLException sql)
        {
			sql.printStackTrace();
			throw new ScalarException(" Could not load Assembly Part Numbers", sql);
		}
        finally
        {
            if(rs != null)
            {
            	try{ rs.close();}catch(Exception e){}
            }
			if(pstmt != null)
            {
				try{ pstmt.close(); }catch(Exception e){}
			}
			if(conn != null)
            {
				try{ conn.close(); }catch(Exception e){}
			}
		}
        return partNumbersList;
    }

    public static void loadAssemblyStages( DBTransaction dbTransaction, StageCycleTimeForm stageCycleTimeForm ) throws ScalarException
    {
       Connection        conn      = null;
       PreparedStatement pstmt     = null;
       ResultSet         rs        = null;
       ArrayList        stages = null;
       StageDetails  stageDetails;

       try
       {
           if ( dbTransaction == null )
           {
               conn = DataSource.getConnection();
           }
           else
           {
               conn = dbTransaction.getConnection();
           }

           pstmt = conn.prepareStatement ( SqlQueryMappings.get("operations.process.stageCycleTime.assemblyStageDetails") );

           pstmt.setInt( 1, stageCycleTimeForm.getProcessstepId() );

           rs = pstmt.executeQuery();

           stages = new ArrayList();

           while ( rs.next() )
           {
               stageDetails = new StageDetails();

               stageDetails.setProcessStepId( rs.getInt( "PROCESSSTEP_ID" ) );
               stageDetails.setStageId( rs.getInt( "STAGE_ID" ) ) ;
               stageDetails.setStageName( rs.getString( "AST_STAGE_NAME" ) );
               stageDetails.setCycleTime( rs.getInt( "AST_Cycle_Time" ) );
               stages.add( stageDetails );
           }

           stageCycleTimeForm.setStageDetails( stages );
       }
       catch(SQLException sql)
       {
           sql.printStackTrace();
           throw new ScalarException(" Could not load Assembly Stages in AssemblyProcess", sql);
       }
       finally
       {
           if(rs != null)
           {
               try{ rs.close();}catch(Exception e){}
           }
           if(pstmt != null)
           {
               try{ pstmt.close(); }catch(Exception e){}
           }
           if(conn != null)
           {
               try{ conn.close(); }catch(Exception e){}
           }
       }

    }

    public static void  saveAssemblyStages ( DBTransaction dbTransaction, ArrayList stageDetailsList ) throws Exception
    {
        Connection conn         = null;
		PreparedStatement pstmt = null;
        boolean success         = false;
        StageDetails stageDetails ;

    	try
        {
			if( dbTransaction == null )
			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = dbTransaction.getConnection();
			}


            pstmt = conn.prepareStatement( SqlQueryMappings.get ( "operations.process.cycleTime.saveAssemblyStageCycleTimeDetails" ) );

            if( stageDetailsList.size() > 0 )
            {
                for( int i = 0; i < stageDetailsList.size(); i++ )
                {
                    stageDetails = ( StageDetails ) stageDetailsList.get( i );
                    pstmt.setInt ( 1, stageDetails.getCycleTime() );
                    pstmt.setInt ( 2, stageDetails.getProcessStepId() );
                    pstmt.setInt ( 3, stageDetails.getStageId() );
                    pstmt.addBatch();
                }
                pstmt.executeBatch();
                success = true;
            }

		}
		catch (Exception sqle)
		{
			sqle.printStackTrace();
			throw new ScalarException ("Could not save Assembly Stage Cycle Time SetUp into database", sqle);
		}
		finally
		{
			if ( dbTransaction == null )
			{
				try
				{
					if (conn != null)
					{
                        if( success == true )
                        {
						    conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
					}
				}
                catch (SQLException ignore) {System.out.println("Sql exception ");}
			}

			if ( pstmt != null )
			{
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore){}
            }
		}
    }
}
